import pandas as pd
import numpy as np

df = pd.read_csv('data/learn_pandas.csv')
print('按照性别求身高中位数\n', df.groupby('Gender')['Height'].median())

gb = df.groupby('Gender')['Height']
print('最小值所在的行\n', gb.idxmin())
print('前百分之五的身高\n', gb.quantile(0.95))

gb = df.groupby('Gender')[['Height', 'Weight']]
print('按照列的迭代计算\n', gb.max())

'''
连接
'''
df1 = pd.DataFrame({'Name': ['San Zhang', 'Si Li'], 'Age': [20, 30]})
df2 = pd.DataFrame({'Name': ['Si Li', 'Wu Wang'], 'Gender': ['F', 'M']})
print('merge连接\n', df1.merge(df2, on='Name', how='left'))

df1 = pd.DataFrame({'Name': ['San Zhang', 'San Zhang'], 'Age': [20, 21], 'Class': ['one', 'two']})
df2 = pd.DataFrame({'Name': ['San Zhang', 'San Zhang'], 'Gender': ['F', 'M'], 'Class': ['two', 'one']})
print('左连接\n', df1.merge(df2, on=['Name', 'Class'], how='left'))

df1 = pd.DataFrame({'Age': [20, 30]}, index=pd.Series(['San Zhang', 'Si Li'], name='Name'))
df2 = pd.DataFrame({'Gender': ['F', 'M']}, index=pd.Series(['Si Li', 'Wu Wang'], name='Name'))
print('索引连接\n', df1.join(df2, how='left'))

'''
缺失值
'''
df = pd.read_csv('data/learn_pandas.csv', usecols=['Grade', 'Name', 'Gender', 'Height', 'Weight', 'Transfer'])
print('查看是否缺失\n', df.isna().head())
print('缺失的比例\n', df.isna().mean())

print('身高缺失的行\n', df[df.Height.isna()].head())

sub_set = df[['Height', 'Weight', 'Transfer']]
print('全部缺失\n', df[sub_set.isna().all(1)])
print('至少缺失一个\n', df[sub_set.isna().any(1)].head())
print('没有缺失\n', df[sub_set.notna().all(1)].head())

# 删除身高体重至少有一个缺失的行
res = df.dropna(how='any', subset=['Height', 'Weight'])
print('删除身高体重至少有一个缺失的行\n', res)

# 删除超过15个缺失值的列
res = df.dropna(1, thresh=df.shape[0] - 15)
print('删除超过15个缺失值的列\n', res)

s = pd.Series([np.nan, 1, np.nan, np.nan, 2, np.nan], list('aaabcd'))
s.fillna(method='ffill')  # 用前面的值向后填充

s.fillna(method='ffill', limit=1)  # 连续出现的缺失，最多填充一次

s.fillna(s.mean())  # value为标量

s.fillna({'a': 100, 'd': 200})  # 通过索引映射填充的值
